Query to find Address Details of Employee - Oracle Fusion HCM

Fusion Queries

Employee Details in Fusion HCM

Query to find Address Details of Employee - Oracle Fusion HCM

Oracle Fusion HCM : 

PERSON_NUMBER,          

ADDRESS_TYPE,          

ADDRESS_LINE_1,          

ADDRESS_LINE_2,          

ADDRESS_LINE_3,          

ADDRESS_LINE_4,          

BUILDING,          

FLOOR_NUMBER,          

TOWN_OR_CITY,          

REGION_1,          

REGION_2,          

REGION_3,  

COUNTRY,          

POSTAL_CODE  

SELECT 

person_number,          

ppauf.address_type,          

paf.address_line_1,          

paf.address_line_2,          

paf.address_line_3,          

paf.address_line_4,          

paf.building,          

paf.floor_number,          

paf.town_or_city,          

paf.region_1,          

paf.region_2,          

paf.region_3,  

per_extract_utility.get_territory_name(paf.country)COUNTRY,          

paf.postal_code     

FROM 

per_all_people_f papf,          

per_person_addr_usages_f ppauf,          

per_addresses_f paf    

WHERE 

papf.person_id = ppauf.person_id(+)          

AND ppauf.address_id = paf.address_id(+)          

AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date  AND TRUNC (SYSDATE) BETWEEN ppauf.effective_start_date AND ppauf.effective_end_date          

AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date

 

ORDER BY person_number